import logging
import time
import redis
import pymysql
import pymysql.cursors
import requests
from app.commons.config import async_exec

# @async_exec
def update_monthly_price_to_cache():
    pool = redis.ConnectionPool(host="106.55.41.183", password="092394572",
                                db=0, decode_responses=True)
    r = redis.Redis(connection_pool=pool)
    with pymysql.connect(
        host="106.55.41.183",
        port=3306,
        user="root",
        password="pj20211112",
        db="snkr",
        autocommit=True,
        cursorclass=pymysql.cursors.DictCursor
    ) as conn:
        with conn.cursor() as cur:
            sql="select distinct username from buyer_stock_info"
            cur.execute(sql)
            userlist=cur.fetchall()
            print(userlist)
            for user in userlist:
                # sql = "SELECT SUM(price) as price, DATE_FORMAT(FROM_UNIXTIME(`ctime`), '%%Y-%%m') AS dt FROM buyer_stock_info WHERE username='%s' GROUP BY dt" % user["username"]
                sql = """SELECT COALESCE(SUM(price), 0) AS price, months.dt
FROM (
    SELECT DATE_FORMAT(date_add('2022-01-01', INTERVAL seq.seq MONTH), '%%Y-%%m') AS dt
    FROM (
        SELECT 0 AS seq UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
    ) AS seq
) AS months
LEFT JOIN (
    SELECT *
    FROM buyer_stock_info
    WHERE username = '%s'
    AND ctime <= UNIX_TIMESTAMP((SELECT MAX(FROM_UNIXTIME(ctime)) FROM buyer_stock_info))
) AS bsi
ON months.dt = DATE_FORMAT(FROM_UNIXTIME(bsi.`ctime`), '%%Y-%%m')
GROUP BY months.dt
"""%(user["username"])
                print(sql)
                cur.execute(sql)
                monthly_price=cur.fetchall()
                print(monthly_price)
                for month in monthly_price:
                    print(month)


def update_buy_time():
    with pymysql.connect(
            host="106.55.41.183",
            port=3306,
            user="root",
            password="pj20211112",
            db="snkr",
            autocommit=True,
            cursorclass=pymysql.cursors.DictCursor
        ) as conn:
            with conn.cursor() as cur:
                sql = """SELECT stock_id, MIN(ctime) AS earliest_time
FROM rent_info
GROUP BY stock_id;
"""
                cur.execute(sql)
                result=cur.fetchall()
                for item in result:
                    sql = "update buyer_stock_info set ctime=%s where id=%s"%(item["earliest_time"],item["stock_id"])
                    cur.execute(sql)
                conn.commit()
if __name__ == '__main__':
    update_monthly_price_to_cache()
    # update_buy_time()